package db.unit.util;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.commons.lang3.StringUtils;
import org.dbunit.database.DatabaseDataSourceConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.excel.XlsDataSet;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class DBUnit {

  public static final String ROOT_URL = "E:/project/xlsFile/";

  private IDatabaseConnection connection;

  private DataSource datasource;

  @Before
  public void setup_dpos测试s02c() throws Exception {
    Properties properties = new Properties();
    properties.setProperty("driverClassName", "com.mysql.jdbc.Driver");
    properties.setProperty("url",
        "jdbc:mysql://rm-bp1c37w230q0832l9o.mysql.rds.aliyuncs.com:3306?useUnicode=yes&characterEncoding=utf-8&useSSL=no");
    properties.setProperty("username", "db0");
    properties.setProperty("password", "mysqldb0");
    properties.setProperty("defaultCatalog", "db2");
    datasource = BasicDataSourceFactory.createDataSource(properties);

    connection = new DatabaseDataSourceConnection(datasource);

  }

  @After
  public void teardown() throws SQLException {
    if (connection != null) {
      connection.close();
    }
  }

  private QueryDataSet getQueryDataSet() throws SQLException {
    return new QueryDataSet(connection);
  }

  private void backupCustom(Map<String, String> map, String fileName) throws Exception {
    QueryDataSet qds = getQueryDataSet();
    for (String tableName : map.keySet()) {

      qds.addTable(tableName, map.get(tableName));
    }

    File tempFile = new File(ROOT_URL, fileName + ".xls");
    XlsDataSet.write(qds, new FileOutputStream(tempFile));

  }
  
  @Test
  public void DPOS_5340() throws Exception {
    Map<String, String> map = new HashMap<>();
    map.put("saleskuday", "select * from db2.saleskuday where shop = 's02c4895' and date = '2017-07-31 00:00:00' or date = '2017-07-30 00:00:00';");
    map.put("shopsku", "select * from db2.shopsku where uuid in (select shopSku from db2.saleskuday where shop = 's02c4895' and date = '2017-07-31 00:00:00' or date = '2017-07-30 00:00:00');");
    map.put("inventory", "select * from db2.inventory where shopSku in (select shopSku from db2.saleskuday where shop = 's02c4895' and date = '2017-07-31 00:00:00' or date = '2017-07-30 00:00:00');");
    backupCustom(map, "初始化报表");
  }

  @Test
  public void createSaleTable() throws Exception {
    String db = "db2";
    String fileName = "初始化";
    saveSale(db,
        Arrays.asList(
            "54c8fab7-f573-402d-af04-52d900d325c4",
            "a285ae3b-fa5f-422f-8846-f6f1cbcaf0d4",
            "72f7f954-e24b-4aab-8c45-4276b14ee35e",
            "ea703238-c285-49a8-a4f5-7c9bd005b19b",
            "1d2a825a-8b95-4619-babb-1c7276b6bac1",
            
            "abc069da-8f89-4767-8c19-68177c74c3ef",
            "bb6220e5-5d05-49ac-9732-566868711d1f",
            "28349b7b-8858-49dd-ad01-3a4b99f45002"
            ),
        fileName);
  }
  
  private void saveSale(String db, List<String> uuids, String fileName) throws Exception {
    Map<String, String> map = new HashMap<>();
    List<String> tmp = new ArrayList<>();
    for (String uuid : uuids) {
      tmp.add("'" + uuid + "'");
    }
    String result = "(" + StringUtils.join(tmp.toArray(), ",") + ")";
    map.put("sale", "SELECT * FROM " + db + ".sale where uuid in " + result);
    map.put("saleline", "select * from " + db + ".saleline where sale in " + result);
    map.put("salepayment", "select * from " + db + ".salepayment where sale in  " + result);

    backupCustom(map, fileName);
  }

}
